/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Materia;
import com.javabeans.MateriaPensum;
/**
 * @author Administrador
 */
public class AdminMateriaPensum implements MateriaPensumManager{
    private Connection c;

    public AdminMateriaPensum(Connection c){
        this.c = c;
    }

    @Override
    public void saveMateriaPensum(MateriaPensum mp) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query="INSERT INTO MATERIA_PENSUM VALUES("+mp.getIdPensum();
            query += ","+mp.getIdCarrera()+","+mp.getIdMateria();
            query+= ","+mp.getLevel();
            query+= ","+mp.isElectiva()+","+mp.getIdPensumPa();
            query+= ","+mp.getIdCarreraPa()+","+mp.getIdMateriaPa()+","+mp.isActivo()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminMateriaPensum saveMateriaPensum(MateriaPensum mp) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<MateriaPensum> getPlanEstPensum(int idPensum) throws DataException{
        List<MateriaPensum> materias = new ArrayList<MateriaPensum>();
        try{
            materias = this.getPlamMaterias(idPensum, true);
        }catch(SQLException e){
            throw new DataException(" AdminMateriaPensum -> getPlanEstPensum() e = "+e.getMessage());
        }
        return materias;
    }
    
    @Override
    public List<MateriaPensum> getPlanEstPensumInactivos(int idPensum) throws DataException{
        List<MateriaPensum> materias = new ArrayList<MateriaPensum>();
        try{
            materias = this.getPlamMaterias(idPensum, false);
        }catch(SQLException e){
            throw new DataException(" AdminMateriaPensum -> getPlanEstPensumInactivos(int idPensum) e = "+e.getMessage());
        }
        return materias;
    }
    
    public List<MateriaPensum> getPlamMaterias(int idPensum, boolean activo) throws DataException{
        List<MateriaPensum> materias = new ArrayList<MateriaPensum>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT MP.IDPENSUM, MP.IDCARRERA, MP.IDMATERIA";
            query += ", MP.LEVEL, MP.ELECTIVA";
            query += ", MP.IDPENSUMPA, MP.IDCARRERAPA, MP.IDMATERIAPA";
            query += ", M.NOMBRE, M.SIGLA";
            query += ", IFNULL(MA.NOMBRE,'') MATERIA_P";
            query += ", IFNULL(MA.SIGLA,'') SIGLA_P";
            query += " FROM MATERIA_PENSUM MP";
            query += " INNER JOIN MATERIA M ON MP.IDCARRERA=M.IDCARRERA AND MP.IDMATERIA=M.IDMATERIA";
            query += " LEFT JOIN (MATERIA_PENSUM MPP INNER JOIN MATERIA MA ON MPP.IDCARRERA=MA.IDCARRERA AND MPP.IDMATERIA=MA.IDMATERIA AND MPP.IDPENSUM="+idPensum+")";
            query += " ON MP.IDPENSUMPA=MPP.IDPENSUM AND MP.IDCARRERAPA=MPP.IDCARRERA AND MP.IDMATERIAPA=MPP.IDMATERIA";
            query += " WHERE MP.IDPENSUM = "+idPensum;
            if(activo)
                query += " AND MP.ACTIVO=1";
            else
                query += " AND MP.ACTIVO=0";
            query += " ORDER BY MP.LEVEL, M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                MateriaPensum mp = new MateriaPensum();
                mp.setIdPensum(idPensum);
                mp.setIdCarrera(rs.getInt("MP.IDCARRERA"));
                mp.setIdMateria(rs.getInt("MP.IDMATERIA"));
                mp.setLevel(rs.getInt("MP.LEVEL"));
                mp.setElectiva(rs.getBoolean("MP.ELECTIVA"));
                mp.setIdPensumPa(rs.getInt("MP.IDPENSUMPA"));
                mp.setIdCarreraPa(rs.getInt("MP.IDCARRERAPA"));
                mp.setIdMateriaPa(rs.getInt("MP.IDMATERIAPA"));
                
                Materia m = new Materia();
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                mp.setMateria(m);
                
                MateriaPensum mpp = new MateriaPensum();
                Materia map = new Materia();
                map.setNombre(rs.getString("MATERIA_P"));
                map.setSigla(rs.getString("SIGLA_P"));
                mpp.setMateria(map);
                mp.setPadreMateria(mpp);
                materias.add(mp);
            }
        }catch(SQLException e){
            throw new DataException(" -> getPlamMaterias(int idPensum, boolean activo) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    public void darBajaElectiva(int idPensum, int idCarrera, int idMateria) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE MATERIA_PENSUM SET ACTIVO = 0 WHERE IDPENSUM="+idPensum+" AND IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminMateriaPensum -> darBajaElectiva(int idPensum, int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    public void darAltaElectiva(int idPensum, int idCarrera, int idMateria) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE MATERIA_PENSUM SET ACTIVO = 1 WHERE IDPENSUM="+idPensum+" AND IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminMateriaPensum -> darBajaElectiva(int idPensum, int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void deleteMatPensum(int idPensum, int idCarrera, int idMateria) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "DELETE FROM GRUPO_MATERIA WHERE IDPENSUM="+idPensum+" AND IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            query = "DELETE FROM MATERIA_PENSUM WHERE IDPENSUM="+idPensum+" AND IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminMateriaPensum -> deleteMatPensum(int idPensum, int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseForDelete(int idPensum, int idCarrera, int idMateria) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDINSCRITO FROM INSCRITO WHERE IDPENSUM="+idPensum+" AND IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException ex){
            throw new DataException(" AdminMateriaPensum -> isUseForDelete(int idPensum, int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public List<MateriaPensum> getMateriasPropuestosPorEst(int idGestion, int idCarrera, int idEstudiante) throws DataException{
        List<MateriaPensum> materias = new ArrayList<MateriaPensum>();
        ResultSet rs = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        PreparedStatement preSt = null;
        boolean flag = false;
        int idPensum = 0;
        int idMateria = 0;
        String idMateriaP = "";
        int i = 0;
        try{
            String query = "SELECT MP.IDPENSUM, MP.IDMATERIA";
            query += ", MP.LEVEL, MP.ELECTIVA";
            query += ", MP.IDPENSUM, MP.IDMATERIAPA";
            query += ", M.NOMBRE, M.SIGLA";
            query += " FROM MATERIA_PENSUM MP";
            query += " INNER JOIN PENSUM P ON MP.IDPENSUM=P.IDPENSUM AND P.VIGENTE=1 AND P.IDCARRERA="+idCarrera;
            query += " INNER JOIN MATERIA M ON MP.IDCARRERA=M.IDCARRERA AND MP.IDMATERIA=M.IDMATERIA";
            query += " INNER JOIN GRUPO_MATERIA GM ON MP.IDPENSUM=GM.IDPENSUM AND MP.IDCARRERA=GM.IDCARRERA AND MP.IDMATERIA=GM.IDMATERIA AND GM.IDGESTION="+idGestion;
            query += " WHERE MP.IDCARRERA = "+idCarrera+" AND MP.LEVEL = 1";
            query += " AND MP.ACTIVO=1";
            query += " GROUP BY MP.IDPENSUM, MP.IDCARRERA, MP.IDMATERIA";
            query += " ORDER BY M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                idPensum = rs.getInt("MP.IDPENSUM");
                idMateria = rs.getInt("MP.IDMATERIA");
                if( this.matAprovado(idPensum, idCarrera, idMateria, idEstudiante, preSt, rs2) ){ // para los de primer semestre
                    flag = true;
                    idMateriaP = idMateria+""; 
                    while(flag){
                        i = 0;
                        i = idMateriaP.indexOf(",");
                        query = "SELECT MP.IDMATERIA";
                        query += ", MP.LEVEL, MP.ELECTIVA";
                        query += ", MP.IDMATERIAPA";
                        query += ", M.NOMBRE, M.SIGLA";
                        query += " FROM MATERIA_PENSUM MP";
                        query += " INNER JOIN PENSUM P ON MP.IDPENSUM=P.IDPENSUM AND P.VIGENTE=1 AND P.IDCARRERA="+idCarrera;
                        query += " INNER JOIN MATERIA M ON MP.IDCARRERA=M.IDCARRERA AND MP.IDMATERIA=M.IDMATERIA";
                        query += " INNER JOIN GRUPO_MATERIA GM ON MP.IDPENSUM=GM.IDPENSUM AND MP.IDCARRERA=GM.IDCARRERA AND MP.IDMATERIA=GM.IDMATERIA AND GM.IDGESTION="+idGestion;
                        query += " WHERE MP.IDPENSUMPA="+idPensum+" AND MP.IDCARRERAPA="+idCarrera;
                        if(i == -1)
                            query += " AND MP.IDMATERIAPA="+idMateriaP;
                        else{
                            query += " AND MP.IDMATERIAPA IN("+idMateriaP+")";
                        }
                        query += " AND MP.ACTIVO=1";
                        query += " GROUP BY MP.IDPENSUM, MP.IDCARRERA, MP.IDMATERIA";                        
                        preSt = c.prepareStatement(query);
                        rs2 = preSt.executeQuery();
                        idMateriaP = ""; // inicializamos el en vacio el codigo
                        i = 0;
                        while(rs2.next()){
                            idMateria = rs2.getInt("MP.IDMATERIA");
                            if( this.matAprovado(idPensum, idCarrera, idMateria, idEstudiante, preSt, rs3) ){
                                idMateriaP += idMateria+",";
                                i++;
                            }else{// la materia no se aprobo
                                MateriaPensum mp = new MateriaPensum();
                                mp.setIdPensum(idPensum);
                                mp.setIdCarrera(idCarrera);
                                mp.setIdMateria(idMateria);
                                mp.setLevel(rs2.getInt("MP.LEVEL"));
                                mp.setElectiva(rs2.getBoolean("MP.ELECTIVA"));
                                mp.setIdPensumPa(idPensum);
                                mp.setIdCarreraPa(idCarrera);
                                mp.setIdMateriaPa(rs2.getInt("MP.IDMATERIAPA"));

                                Materia m = new Materia();
                                m.setNombre(rs2.getString("M.NOMBRE"));
                                m.setSigla(rs2.getString("M.SIGLA"));
                                mp.setMateria(m);
                                materias.add(mp);
                            }
                        }
                        if(i > 0){
                            i = idMateriaP.lastIndexOf(",");
                            idMateriaP = idMateriaP.substring(0, i);
                        }else{
                            flag = false;
                        }
                    }
                }else{
                    MateriaPensum mp = new MateriaPensum();
                    mp.setIdPensum(idPensum);
                    mp.setIdCarrera(idCarrera);
                    mp.setIdMateria(idMateria);
                    mp.setLevel(1);
                    mp.setElectiva(rs.getBoolean("MP.ELECTIVA"));
                    mp.setIdPensumPa(idPensum);
                    mp.setIdCarreraPa(idCarrera);
                    mp.setIdMateriaPa(rs.getInt("MP.IDMATERIAPA"));
                    Materia m = new Materia();
                    m.setNombre(rs.getString("M.NOMBRE"));
                    m.setSigla(rs.getString("M.SIGLA"));
                    mp.setMateria(m);
                    materias.add(mp);
                }
            }
        }catch(SQLException e){
            throw new DataException(" -> getMateriasPropuestosPorEst(int idCarrera, int idEstudiante) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closeRs(rs2);
            SqlManager.closeRs(rs3);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    public boolean matAprovado(int idPen, int idCarr, int idMat, int idEst, PreparedStatement preSt,ResultSet rs)throws DataException{
        boolean res = false;
        try{
            String query = "SELECT IDINSCRITO";
            query += " FROM INSCRITO";
            query += " WHERE IDPENSUM="+idPen+" AND IDCARRERA="+idCarr;
            query += " AND IDMATERIA="+idMat;
            query += " AND IDESTUDIANTE="+idEst+" AND APROBADO=1 LIMIT 1";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" -> matAprovado(int idPen, int idCarr, int idMat..) e = "+e.getMessage());
        }
        return res;
    }
    
}
